package com.orderplus.opshop.common.excel;

import com.orderplus.opshop.common.util.JSONUitl;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by zhouxj6112 on 2019/1/3.
 */
public class ExcelUtil {

	private static final String EXCEL_XLS = "xls";
	private static final String EXCEL_XLSX = "xlsx";

	private static Workbook getWorkbok(File file) throws IOException {
		Workbook wb = null;
		FileInputStream in = new FileInputStream(file);
		if (file.getName().endsWith(EXCEL_XLS)) {            // Excel 2003
			wb = new HSSFWorkbook(in);
		} else if (file.getName().endsWith(EXCEL_XLSX)) {    // Excel 2007/2010
			wb = new XSSFWorkbook(in);
		}
		return wb;
	}

	/**
	 * 写入到一个excel文件里面去
	 *
	 * @param dataList
	 * @param finalXlsxPath
	 */
	public static void writeExcel(List<Map<String, Object>> dataList, String finalXlsxPath) {
		OutputStream out = null;
		try {
			// 获取总列数
			int columnNumCount = dataList.get(0).size();
			// 读取Excel空白文档
			File finalXlsxFile = new File(finalXlsxPath);
			if (!finalXlsxFile.exists()) {
				// 在该路径下创建一个空白excel表格文件
				Workbook xWorkbook = null;
				if (finalXlsxPath.endsWith(EXCEL_XLSX)) {
					xWorkbook = new XSSFWorkbook();
				} else if (finalXlsxPath.endsWith(EXCEL_XLS)) {
					xWorkbook = new HSSFWorkbook();
				}
				xWorkbook.createSheet("工作表1");
				out = new FileOutputStream(finalXlsxPath);
				xWorkbook.write(out);
				out.flush();
				out.close();
			}

			Workbook workBook = getWorkbok(finalXlsxFile);
			// sheet 对应一个工作页
			Sheet sheet = workBook.getSheetAt(0);
			/**
			 * 删除原有数据，除了属性列
			 */
			int rowNumber = sheet.getLastRowNum();    // 第一行从0开始算
			System.out.println("原始数据总行数，除属性列：" + rowNumber);
			if (rowNumber == 0) {
				// 创建第一行标题栏
				Row row = sheet.createRow(0);
				int n = 0;
				for (Map.Entry<String, Object> entry : dataList.get(0).entrySet()) {
					String key = entry.getKey();
					Cell cell = row.createCell(n);
					cell.setCellValue(key);
					CellStyle cellStyle = workBook.createCellStyle();
					cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
					cellStyle.setVerticalAlignment(CellStyle.ALIGN_CENTER);
					cell.setCellStyle(cellStyle);
					n++;
				}
			} else {
				// 已经存在,就清除历史数据
				for (int i = 1; i <= rowNumber; i++) {
					Row row = sheet.getRow(i);
					sheet.removeRow(row);
				}
			}

			// 创建文件输出流，输出电子表格：这个必须有，否则你在sheet上做的任何操作都不会有效
			out = new FileOutputStream(finalXlsxPath);
			workBook.write(out);

			// 读取第一行数据的顺序
			Row firstrRow = sheet.getRow(0);
			HashMap<String, Integer> sample = new HashMap();
			for (int i = 0; i < firstrRow.getPhysicalNumberOfCells(); i++) {
				Cell cell = firstrRow.getCell(i);
				String key = cell.getStringCellValue();
				sample.put(key, i);
			}

			// 往Excel中写新数据
			for (int j = 0; j < dataList.size(); j++) {
				// 创建一行：从第二行开始，跳过属性列
				Row row = sheet.createRow(j + 1);
				// 得到要插入的每一条记录
				Map<String, Object> dataMap = dataList.get(j);
				// 在一行内循环遍历列
				for (Map.Entry<String, Object> entry : dataMap.entrySet()) {
					String key = entry.getKey();
					String value = entry.getValue().toString(); // 必须String
					// 找到它所在的索引
					Integer k = sample.get(key);
					Cell cell = row.createCell(k);
					cell.setCellValue(value);
				}
			}
			// 创建文件输出流，准备输出电子表格：这个必须有，否则你在sheet上做的任何操作都不会有效
			out = new FileOutputStream(finalXlsxPath);
			workBook.write(out);

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (out != null) {
					out.flush();
					out.close();
				}
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		System.out.println("数据导出成功");
	}

	/**
	 * 读取excel文件
	 *
	 * @param filePath
	 * @return
	 */
	public static List<Map> readExcel(String filePath) {
		List result = new ArrayList();
		try {
			File file = new File(filePath);
			Workbook wb = getWorkbok(file);
			Sheet sheet = wb.getSheetAt(0);

			Row allKeys = sheet.getRow(0);
			HashMap sample = new HashMap();
			int column = allKeys.getPhysicalNumberOfCells();
			for (int i = 0; i < column; i++) {
				String key = allKeys.getCell(i).getStringCellValue();
				sample.put(key, i);
			}
			//
			int row = sheet.getPhysicalNumberOfRows();
			for (int i = 1; i < row; i++) {
				Row rows = sheet.getRow(i);
				HashMap<String, Object> hashMap = new HashMap(sample);
				for (int j = 0; j < column; j++) {
					Cell cell = rows.getCell(j);
					String value = "";
					if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
						value = "" + cell.getNumericCellValue();
					} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
						value = cell.getBooleanCellValue() ? "1" : "0";
					} else {
						value = cell.getStringCellValue();
					}
					for (Map.Entry<String, Object> entry : hashMap.entrySet()) {
						Object obj = entry.getValue();
						if (obj instanceof Integer && (Integer) obj == j) {
							hashMap.put(entry.getKey(), value);
							break;
						}
					}
				}
				result.add(hashMap);
			}

		} catch (IOException e) {
			e.printStackTrace();
		}
		return result;
	}

	/**
	 * 读取树装结构excel文件
	 *
	 * @param filePath
	 * @return
	 */
	public static List<Map> readGoogleTreeExcel(String filePath) {
		List result = new ArrayList();
		try {
			File file = new File(filePath);
			Workbook wb = getWorkbok(file);
			Sheet sheet = wb.getSheetAt(0);

			//
			int row = sheet.getPhysicalNumberOfRows();
			for (int i = 0; i < row; i++) {
				Row rows = sheet.getRow(i);
				//
				int column = rows.getPhysicalNumberOfCells();
				Cell first = rows.getCell(0); // id
				Cell last = rows.getCell(column - 1); // 最底层名称

				int id = (int) first.getNumericCellValue();
				String name = last.getStringCellValue();

				Map line = new HashMap<>();
				line.put("id", id);
				line.put("name", name);
				line.put("depth", column - 1);
				line.put("depthPath", "/" + id + "/");
				line.put("parentId", 0);
				// 找到它的上级
				if (column > 2) {
					line.put("depthPath", "/");
					for (int n = 1; n < column - 1; n++) {
						String middle = rows.getCell(n).getStringCellValue();
						int pid = 0;
						for (int k = 0; k < result.size(); k++) {
							Map m = (Map) result.get(k);
							String mName = (String) m.get("name");
							if (mName.equals(middle)) {
								pid = (int) m.get("id");
								break;
							}
						}
						if (pid > 0) {
							String depthPath = (String) line.get("depthPath");
							line.put("depthPath", depthPath + pid + "/");
							if (n == column - 2) {
								line.put("parentId", pid);
							}
						}
					}
					// 最后一行
					line.put("depthPath", (String) line.get("depthPath") + id + "/");
				}
				result.add(line);
			}

		} catch (IOException e) {
			e.printStackTrace();
		}
		return result;
	}

	public static void main(String[] args) {
		List list0 = ExcelUtil.readGoogleTreeExcel("/Users/MrZhou/Documents/OrderPlus/b2c-admin/src/main/resources/template/taxonomy-with-ids.en-US.xls");
		System.out.println(JSONUitl.obj2String(list0));

		List list = ExcelUtil.readExcel("/Users/MrZhou/Documents/OrderPlus/b2c-admin/src/main/resources/template/翻译检查.xlsx");
		System.out.println(JSONUitl.obj2String(list));
		ExcelUtil.writeExcel(list, "/Users/MrZhou/Documents/OrderPlus/b2c-admin/src/main/resources/template/test.xlsx");
	}

}
